package com.abc.easyclaim;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import com.abc.bean.Comment;
import com.abc.bean.Photo;
import com.abc.bean.Case;
import com.abc.bean.Record;
import com.abc.bean.Text;
import com.abc.db.DBManager;
import com.abc.tool.StringTool;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DatabaseDump {
	private String mDestXmlFilename; 
    private static SQLiteDatabase mDb; 
    private static DBManager dbManager;
    private static Case caze=new Case();
    private static Photo photo=new Photo();
    private static Record record=new Record();
    private static Text text=new Text();
    private static Comment comment=new Comment();
    private static List<Case> cazes=new ArrayList<Case>();              //caze集合
    public DatabaseDump(SQLiteDatabase db, String destXml) {   	
        mDb = db; 
        mDestXmlFilename = destXml; 
    } 
    
    public void exportData(ArrayList<File> listFiles , File zipFile ,int cazeID) {  	 
        try { 
            String sql = "SELECT * FROM sqlite_master "; 
            Cursor cur = mDb.rawQuery(sql, new String[0]); 
            cur.moveToFirst(); 
            String tableName; 
            while (cur.getPosition() < cur.getCount()) { 
                tableName = cur.getString(cur.getColumnIndex("name"));  
                if (!tableName.equals("android_metadata") && !tableName.equals("sqlite_sequence") && !tableName.equals("template")) { 
                    writeExcel(tableName ,cazeID); 
                    File xlsfile=new File(mDestXmlFilename + tableName + ".xls");
                    listFiles.add(xlsfile);
                } 
                cur.moveToNext(); 
            } 
            //压缩所有文件
            ZipUtil.zipFiles(listFiles, zipFile);
            //删除xls文件
            for (int i = 0; i < listFiles.size(); i++) {
            	if ((listFiles.get(i)).getName().split("\\.")[1].equals("xls")) {
            		(listFiles.get(i)).delete();
				}
			}
        } catch (Exception e) { 
            e.printStackTrace(); 
        } 
    } 
    
    /**
     * 生成一个Excel文件
     * 
     * @param fileName
     * 要生成的Excel文件名
     */ 
    public void writeExcel(String tableName ,int cazeID) { 

        WritableWorkbook wwb = null; 
        String fileName; 
        fileName=mDestXmlFilename + tableName + ".xls";
        int r = 0; 
        String sql;
        if (tableName.equals("caze")) {
        	sql = "select * from " + tableName + " where id=" +cazeID;
		}
        else {
        	sql = "select * from " + tableName + " where caseId=" +cazeID;
		}
        Cursor cur = mDb.rawQuery(sql, null); 
        int numcols = cur.getColumnCount(); 
        int numrows = cur.getCount(); 
        String records[][] = new String[numrows + 1][numcols];// 存放答案，多一行标题行 
        if (cur.moveToFirst()) { 
            while (cur.getPosition() < cur.getCount()) { 
                for (int c = 0; c < numcols; c++) { 
                    if (r == 0) { 
                        records[r][c] = cur.getColumnName(c); 
                        records[r + 1][c] = cur.getString(c); 
                    } else { 
                        records[r + 1][c] = cur.getString(c); 
                    } 
                } 
                cur.moveToNext(); 
                r++; 
            } 
            cur.close(); 
        } 
        try { 
            // 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象 
            wwb = Workbook.createWorkbook(new File(fileName)); 
        } catch (IOException e) { 
            e.printStackTrace(); 
        } 
        if (wwb != null) { 
            // 创建一个可写入的工作表 
            // Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置 
            WritableSheet ws = wwb.createSheet("sheet1", 0); 

            // 下面开始添加单元格 
            for (int i = 0; i < numrows + 1; i++) { 
                for (int j = 0; j < numcols; j++) { 
                    // 这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行 
                    Label labelC = new Label(j, i, records[i][j]);  
                    try { 
                        // 将生成的单元格添加到工作表中 
                        ws.addCell(labelC); 
                    } catch (RowsExceededException e) { 
                        e.printStackTrace(); 
                    } catch (WriteException e) { 
                        e.printStackTrace(); 
                    } 
                } 
            } 
            try { 
                // 从内存中写入文件中 
                wwb.write(); 
                // 关闭资源，释放内存 
                wwb.close(); 
            } catch (IOException e) { 
                e.printStackTrace(); 
            } catch (WriteException e) { 
                e.printStackTrace(); 
            } 
        } 
    } 
    
    public static void readExcel(File excelFile) throws BiffException, IOException{
	    //创建一个list用来存读取的内容
	    Workbook rwb = null;
	    Cell cell = null;    
	    dbManager=MainApplication.getMgr();
	    cazes=dbManager.queryCases();
	    int cazeId=cazes.get(cazes.size()-1).getId();
	    String tableName=excelFile.getName().split("\\.")[0];
	    //创建输入流
	    InputStream stream = new FileInputStream(excelFile);
	    //获取Excel文件对象
	    rwb = Workbook.getWorkbook(stream);
	    //获取文件的指定工作表 默认的第一个
	    Sheet sheet = rwb.getSheet(0);
	    //行数(表头的目录不需要，从1开始)
	    System.out.println("sheet.getRows()="+sheet.getRows());
	    for(int i = 1;i<sheet.getRows();i++){
		    //列数
		    for(int j =0;j<sheet.getColumns();j++){
		    	//String[] str = new String[sheet.getColumns()];
			    //获取第i行，第j列的值
			    cell = sheet.getCell(j, i);
			    String cellString=new String(cell.getContents().getBytes("GBK"),"GBK");  
			    if (tableName.equals("caze")) {	
					System.out.println("cell.getContents()="+cell.getContents());
					switch (j) {
					case 0:
						break;
					case 1:
						caze.setName(cellString);
						break;
					case 2:
						caze.setAccidentDate(cellString);
						break;
					case 3:
						caze.setAccidentAddress(cellString);
						break;
					case 4:
						caze.setLastModifyDate(cellString);
						break;
					case 5:
						caze.setCarNumber(cellString);
						break;
					case 6:
						caze.setCarOwnerName(cellString);
						break;
					case 7:
						caze.setCarOwnerPhone(cellString);
						break;
					case 8:
						caze.setCarOwnerAddr(cellString);
						break;
					case 9:
						caze.setInsuranceNumber(cellString);
						break;
					case 10:
						caze.setNote(cellString);
						break;
					case 11:
						caze.setOfficerName(cellString);
						break;
					case 12:
						caze.setOfficerNumber(cellString);
						break;
					case 13:
						caze.setImportant(cellString);
						break;
					case 14:
						caze.setLocation(cellString);
						break;
					case 15:
						caze.setCarBrand(cellString);
						break;
					}		
				}
			    else if (tableName.equals("photo")) {			
					switch (j) {
					case 0:
						break;
					case 1:
						photo.setTitle(cellString);
						break;
					case 2:
						photo.setDescription(cellString);
						break;
					case 3:
						photo.setDate(StringTool.string2Date(cellString, "yyyy-MM-dd HH:mm:ss"));
						break;
					case 4:
						photo.setPath(cellString);
						break;
					case 5:
						photo.setSize(cellString);
						break;
					case 6:
						System.out.println("caze.getId()="+caze.getId());
						photo.setCaseId(cazeId);
						break;
					}	
				}
			    else if (tableName.equals("record")) {
					switch (j) {
					case 0:
						break;
                    case 1:
						record.setTitle(cellString);
						break;
                    case 2:
						record.setDescription(cellString);
						break;
                    case 3:
                    	record.setDate(StringTool.string2Date(cellString, "yyyy-MM-dd HH:mm:ss"));
                    	break;
                    case 4:
                    	record.setPath(cellString);
                    	break;
                    case 5:
                    	record.setLength(Integer.parseInt(cellString));
                    	break;
                    case 6:
                    	record.setCaseId(cazeId);
					}	
				}
			    else if (tableName.equals("text")) {
					switch (j) {
					case 0:
						break;
					case 1:
						text.setTitle(cellString);
						break;
					case 2:
						text.setContent(cellString);
						break;
					case 3:
						text.setLastModifyDate(cellString);
						break;
					case 4:
						text.setCaseId(cazeId);
						break;
					}
				}
			    else if (tableName.equals("comment")) {
					switch (j) {
					case 0:
						break;
					case 1:
						comment.setDate(cellString);
						break;
					case 2:
						comment.setContent(cellString);
						break;
					case 3:
						comment.setType(cellString);
						break;
					case 4:
						comment.setCaseId(cazeId);
						break;
					}
				}
		    }
		    if (tableName.equals("caze")) {
		    	dbManager.addCase(caze);
			}
		    else if (tableName.equals("photo")) {
		    	dbManager.addPhoto(photo);
			}
		    else if (tableName.equals("record")) {
		    	dbManager.addRecord(record);
			}
		    else if (tableName.equals("text")) {
		    	dbManager.addText(text);
			}
		    else if (tableName.equals("comment")) {
		    	dbManager.addComment(comment);
		    }
	    }
	    
    }
}
